package org.jdu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.log4j.Logger;
import org.jdu.dao.config.DbParam;
import org.jdu.dao.config.Query;
import org.jdu.exception.ServiceException;
import org.jlu.logger.PrdLoggerFactory;


public class DAOServiceLob {

	private DAOService daoService;
	private Logger log = PrdLoggerFactory.getInstance().getLogger("crparch");

	public DAOServiceLob(DAOService daoService) {
		this.daoService = daoService;
	}

	public boolean insertLob(String dbName, Query insert, Query select,
			Query update) throws ServiceException {
		log.info("[DAOServiceLob::insertBlob] BEGIN");
		log.info("[DAOServiceLob::insertBlob] Inserisco BLOB vuoto");
		// boolean result = daoService.update(dbName, insert);
		Connection con = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try {
			con = daoService.getDbConnection().getConnection(dbName);
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(insert.getSql());
			daoService.addParametersToQuery(insert, pstmt);

			int rows = pstmt.executeUpdate();
			result = updateLob(con, dbName, select, update);
			con.commit();
		} catch (DbException e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		} catch (SQLException e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		} catch (ServiceException e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw e;
		} catch (Exception e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		} finally {
			daoService.closeConnection(con, pstmt, null);
		}
		return result;
	}

	private void rollback(Connection con) throws ServiceException {
		try {
			con.rollback();
		} catch (SQLException e1) {
			log.error("[DAOServiceLob::insertLob()]  " + e1.getMessage(), e1);
			throw new ServiceException(e1.getMessage());
		}
	}

	public boolean updateLob(String dbName, Query select, Query update)
			throws ServiceException {
		boolean result = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = daoService.getDbConnection().getConnection(dbName);
			con.setAutoCommit(false);

			result = updateLob(con, dbName, select, update);
			con.commit();
		} catch (DbException e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		} catch (SQLException e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		} catch (ServiceException e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw e;
		} catch (Exception e) {
			rollback(con);
			log.error("[DAOServiceLob::insertLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		} finally {
			daoService.closeConnection(con, pstmt, null);
		}
		return result;
	}

	private boolean updateLob(Connection con, String dbName, Query select,
			Query update) throws ServiceException {
		log.info("[DAOServiceLob::updateBlob] BEGIN");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean result;
		try {
			// con = daoService.getDbConnection().getConnection(dbName);
			if (con == null) {
				con = daoService.getDbConnection().getConnection(dbName);
			}
			pstmt = con.prepareStatement(select.getSql());
			Map lobFields = new HashMap();
			log.info("[DAOServiceLob::updateLob] Query: " + select.getSql());
			daoService.addParametersToQuery(select, pstmt);
			rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			if (rs.next()) {
				int colcount = rsmd.getColumnCount();
				for (int i = 0; i < colcount; i++) {
					String columnName = rsmd.getColumnName(i + 1);
					int columnType = rsmd.getColumnType(i + 1);
					switch (columnType) {
					case Types.BLOB:
						lobFields.put(columnName.toUpperCase(), rs
								.getBlob(i + 1));
						break;
					case Types.CLOB:
						lobFields.put(columnName.toUpperCase(), rs
								.getClob(i + 1));
						break;
					}
				}
			}
			rs.close();
			pstmt.close();
			Collection c = update.getParams();
			for (Iterator iterator = c.iterator(); iterator.hasNext();) {

				DbParam dbParam = (DbParam) iterator.next();
				if (dbParam.getType() == DbParam.BLOB
						|| dbParam.getType() == DbParam.CLOB) {
					if (dbParam.getColumnName() == null) {
						throw new ServiceException(
								"Attenzione!! L'oggetto DbParam " + dbParam
										+ " non ha specificato il columnName");
					}
					Object lob = lobFields.get(dbParam.getColumnName()
							.toUpperCase());
					log
							.debug("[DAOServiceLob::updateLob] Lob vuoto recuperato: "
									+ lob);
					dbParam.setLob(lob);
				}
			}
			pstmt = con.prepareStatement(update.getSql());
			daoService.addParametersToQuery(update, pstmt);
			result = pstmt.executeUpdate() > 0;
			return result;
		} catch (SQLException e) {
			rollback(con);
			log.error("[DAOServiceLob::updateLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		} catch (Exception e) {
			rollback(con);
			log.error("[DAOServiceLob::updateLob()]  " + e.getMessage(), e);
			throw new ServiceException(e.getMessage());
		}
	}

}
